﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GAMS.MODEL;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_MAS_AccountOLD // : IDAL_ACM_MAS_Account
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();
        public M_ACM_MAS_Account GetInfo_ByAccountSeq(string account_seq)
        {
            DataRowCollection drc = null;
            M_ACM_MAS_Account info = new M_ACM_MAS_Account();
            string strSQL = "";

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@account_seq", account_seq);
                strSQL = "SELECT acc.account_seq, acc.account_code, acc.upper_account_seq, acc.account_name, acc.account_descr" +
                         ", acc.record_status, acc.version_seq, acc.created_by , acc.created_when, acc.updated_by, acc.updated_when " +
                         "FROM acm_mas_account acc " +
                         "LEFT JOIN acm_mas_account acc2 ON acc.upper_account_seq = acc2.account_seq " +
                         "WHERE (acc.account_seq=@account_seq) and " +
                         "      (acc.record_status = 'D')";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.account_seq = Convert.ToInt32(drc[0]["account_seq"]);
                    info.account_code = drc[0]["account_code"].ToString();
                    info.upper_account_seq = cn.ConvertNullInt(drc[0]["upper_account_seq"]);
                    info.account_name = drc[0]["account_name"].ToString();
                    info.account_desc = drc[0]["account_descr"].ToString();
                    info.record_status = drc[0]["record_status"].ToString();
                    info.version_seq = drc[0]["version_seq"].ToString();
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_ACM_MAS_Account GetInfo_ByAccountCode(string account_code, string version_seq)
        {
            DataRowCollection drc = null;
            M_ACM_MAS_Account info = new M_ACM_MAS_Account();
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@account_code", account_code);
                dbManager.AddParameters(1, "@version_seq", version_seq);
                strSQL = "SELECT acc.account_seq, acc.account_code, acc.upper_account_seq, acc.account_name, acc.account_descr" +
                         ", acc.record_status, acc.version_seq, acc.created_by , acc.created_when, acc.updated_by, acc.updated_when " +
                         "FROM acm_mas_account acc " +
                         "LEFT JOIN acm_mas_account acc2 ON acc.upper_account_seq = acc2.account_seq " +
                         "WHERE (acc.account_code=@account_code) and " +
                         "      (acc.version_seq=@version_seq) and " +
                         "      (acc.record_status = 'D')";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.account_seq = Convert.ToInt32(drc[0]["account_seq"]);
                    info.account_code = drc[0]["account_code"].ToString();
                    info.upper_account_seq = cn.ConvertNullInt(drc[0]["upper_account_seq"]);
                    info.account_name = drc[0]["account_name"].ToString();
                    info.account_desc = drc[0]["account_descr"].ToString();
                    info.record_status = drc[0]["record_status"].ToString();
                    info.version_seq = drc[0]["version_seq"].ToString();
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void GetAccountSeq()
        {
            DataRowCollection drc = null;
            M_ACM_MAS_Account info = new M_ACM_MAS_Account();
            string strSQL = "";

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(1);
                strSQL = "SELECT COUNT(account_seq)+1 as acc_seq" +
                         "FROM acm_mas_account " +
                         "GROUP BY account_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.account_seq = Convert.ToInt32(drc[0]["acc_seq"]);
                }
                else
                {
                    info.account_seq = 1;
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            // return info;
        }

        public void GetMaxVersion()
        {
            DataRowCollection drc = null;
            M_ACM_MAS_Account info = new M_ACM_MAS_Account();
            string strSQL = "";

            try
            {

                dbManager.Open();
                strSQL = "SELECT MAX(version_seq)+1 as Seq_No " +
                         "FROM acm_mas_account ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.version_seq = drc[0]["Seq_No"].ToString();
                }
                else
                {
                    info.account_seq = 1;
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            // return info;
        }

        public IList<M_ACM_MAS_Account> VersionNo()
        {
            string strSQL = "";
            DataRowCollection drc = null;
            IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();


            try
            {
                dbManager.Open();
                strSQL = "SELECT version_seq " +
                         "FROM acm_mas_account " +
                         "WHERE record_status = 'D' " +
                         "GROUP BY version_seq " +
                         "ORDER BY version_seq Desc";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_MAS_Account info = new M_ACM_MAS_Account();
                        info.version_seq = drc[i]["version_seq"].ToString();
                        infoList.Add(info);
                    }
                }
                else
                {
                    M_ACM_MAS_Account info2 = new M_ACM_MAS_Account();
                    info2.version_seq = "1";
                    infoList.Add(info2);
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_MAS_Account> GetFullRoot(string ver_no)
        {
            DataRowCollection drc = null;
            IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@ver_no", ver_no);

                strSQL = "SELECT account_seq,account_code,upper_account_seq,account_name,account_descr" +
                         ",record_status,version_seq, created_by ,created_when,updated_by,updated_when " +
                         ", concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as full_name " +
                         "FROM acm_mas_account " +
                         "WHERE (version_seq=@ver_no) and " +
                         "      ((upper_account_seq is null) or (upper_account_seq =0) Or (upper_account_seq=''))" +
                         "      and (record_status = 'D')";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_MAS_Account info = new M_ACM_MAS_Account();
                        info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
                        info.account_code = drc[i]["account_code"].ToString();
                        info.upper_account_seq = cn.ConvertNullInt(drc[i]["upper_account_seq"]);
                        info.account_name = drc[i]["account_name"].ToString();
                        info.account_desc = drc[i]["account_descr"].ToString();
                        info.record_status = drc[i]["record_status"].ToString();
                        info.version_seq = drc[i]["version_seq"].ToString();
                        info.full_name = drc[i]["full_name"].ToString();

                        infoList.Add(info);
                    }
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_ACM_MAS_Account> GetFullParent(string strAccountSeq)
        {
            Nullable<int> account_seq = cn.ConvertNullInt(strAccountSeq);
            DataRowCollection drc = null;
            IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@account_seq", account_seq);

                strSQL = "SELECT acc.account_seq, acc.account_code, acc.upper_account_seq, acc.account_name, acc.account_descr" +
                         ", acc.record_status, acc.version_seq, acc.created_by , acc.created_when, acc.updated_by, acc.updated_when " +
                         ", concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, '')) as full_name " +
                         ", acc2.count as child_count " +
                         "FROM acm_mas_account acc " +
                         "LEFT JOIN (SELECT upper_account_seq, count(*) as count " +
                                    "FROM acm_mas_account " +
                                    "WHERE (record_status = 'D') " +
                                    "GROUP BY upper_account_seq " +
                                    ") acc2 ON acc.account_seq = acc2.upper_account_seq " +
                         "WHERE (acc.upper_account_seq=@account_seq) and " +
                         "      (acc.record_status = 'D')";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_MAS_Account info = new M_ACM_MAS_Account();
                        info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
                        info.account_code = drc[i]["account_code"].ToString();
                        info.upper_account_seq = cn.ConvertNullInt(drc[i]["upper_account_seq"]);
                        info.account_name = drc[i]["account_name"].ToString();
                        info.account_desc = drc[i]["account_descr"].ToString();
                        info.record_status = drc[i]["record_status"].ToString();
                        info.version_seq = drc[i]["version_seq"].ToString();
                        info.full_name = drc[i]["full_name"].ToString();
                        info.child_count = cn.ConvertNullInt(drc[i]["child_count"]);

                        infoList.Add(info);
                    }
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(M_ACM_MAS_Account info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@account_code", info.account_code);
                dbManager.AddParameters(1, "@upper_account_seq", info.upper_account_seq);
                dbManager.AddParameters(2, "@account_name", info.account_name);
                dbManager.AddParameters(3, "@account_desc", info.account_desc);
                dbManager.AddParameters(4, "@version_seq", info.version_seq);
                dbManager.AddParameters(5, "@record_status", info.record_status);
                dbManager.AddParameters(6, "@created_by", info.created_by);
                dbManager.AddParameters(7, "@created_when", info.created_when);

                strSQL = "INSERT INTO acm_mas_account "
                    + " (account_code,upper_account_seq "
                    + ", account_name,account_descr ,version_seq "
                    + ", record_status, created_by, created_when)"
                    + "  VALUES("
                    + "  @account_code"
                    + ", @upper_account_seq"
                    + ", @account_name"
                    + ", @account_desc"
                    + ", @version_seq"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_ACM_MAS_Account info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@account_seq", info.account_seq);
                dbManager.AddParameters(1, "@account_code", info.account_code);
                dbManager.AddParameters(2, "@upper_account_seq", info.upper_account_seq);
                dbManager.AddParameters(3, "@account_name", info.account_name);
                dbManager.AddParameters(4, "@account_descr", info.account_desc);
                dbManager.AddParameters(5, "@version_seq", info.version_seq);
                dbManager.AddParameters(6, "@record_status", info.record_status);
                dbManager.AddParameters(7, "@updated_by", info.updated_by);
                dbManager.AddParameters(8, "@updated_when", info.updated_when);

                strSQL = "UPDATE acm_mas_account"
                    + "  SET account_code = @account_code"
                    + ", upper_account_seq = @upper_account_seq"
                    + ", account_name = @account_name"
                    + ", account_descr = @account_descr"
                    + ", version_seq = @version_seq"
                    + ", record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE account_seq = @account_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void SetNoneActiveVersion(M_ACM_MAS_Account info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@version_seq", info.version_seq);
                strSQL = " UPDATE acm_mas_account"
                      + " SET record_status = 'U'"
                      + " WHERE version_seq = @version_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
            }
        }

        public void ClearField(M_ACM_MAS_Account info)
        {
            try
            {
                string strSQL = "SELECT account_seq,account_code,upper_account_seq,account_name " +
                         ", account_descr,record_status,version_seq,created_by " +
                         ", created_when,updated_by,updated_when " +
                         "FROM acm_mas_account ";
                         //"WHERE 1 = 0";
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }


    }
}
